import sqlite3
import os
import datetime

def main():
    print("=== 交易记录数据库简化修复工具 ===")
    
    # 1. 检查当前目录和数据库文件
    check_files()
    
    # 2. 备份数据库
    backup_path = backup_database()
    if backup_path:
        print(f"数据库已备份到: {backup_path}")
    
    # 3. 重新创建正确的表结构
    recreate_table()
    
    # 4. 测试修复后的功能
    test_insert()
    
    print("\n=== 简化修复完成 ===")
    print("请手动检查并更新程序中的insert_trade_record函数实现：")
    print("1. 确保使用正确的表结构：product_name, direction, offset, volume, price, reason, timestamp, created_at")
    print("2. 确保每次交易后都正确调用了该函数")

# 检查文件
def check_files():
    print(f"当前工作目录: {os.getcwd()}")
    db_path = os.path.join(os.getcwd(), 'trade_monitor.db')
    if os.path.exists(db_path):
        size = os.path.getsize(db_path) / 1024  # KB
        mtime = datetime.datetime.fromtimestamp(os.path.getmtime(db_path))
        print(f"发现数据库文件: trade_monitor.db ({size:.2f} KB, 修改时间: {mtime})")
    else:
        print("警告: 未找到trade_monitor.db文件")

# 备份数据库
def backup_database():
    try:
        if not os.path.exists('trade_monitor.db'):
            print("没有找到数据库文件，无法备份")
            return None
        
        backup_time = datetime.datetime.now().strftime('%Y%m%d_%H%M%S')
        backup_path = f'trade_monitor_backup_{backup_time}.db'
        
        with open('trade_monitor.db', 'rb') as f_in:
            with open(backup_path, 'wb') as f_out:
                f_out.write(f_in.read())
        
        return backup_path
    except Exception as e:
        print(f"备份数据库时出错: {str(e)}")
        return None

# 重新创建表结构
def recreate_table():
    try:
        conn = sqlite3.connect('trade_monitor.db')
        cursor = conn.cursor()
        
        # 检查是否已有trade_records表
        cursor.execute("SELECT name FROM sqlite_master WHERE type='table' AND name='trade_records';")
        table_exists = cursor.fetchone() is not None
        
        if table_exists:
            # 重命名现有表以便保留数据
            try:
                cursor.execute("ALTER TABLE trade_records RENAME TO trade_records_old;")
                print("已重命名现有表为trade_records_old")
            except Exception as e:
                print(f"重命名表时出错: {str(e)}")
                print("尝试直接创建新表...")
        
        # 创建新的trade_records表，使用正确的结构
        create_table_sql = '''
        CREATE TABLE IF NOT EXISTS trade_records (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            product_name TEXT NOT NULL,
            direction TEXT NOT NULL,
            offset TEXT NOT NULL,
            volume REAL NOT NULL,
            price REAL NOT NULL,
            reason TEXT NOT NULL,
            timestamp TEXT NOT NULL,
            created_at TEXT NOT NULL
        );
        '''
        cursor.execute(create_table_sql)
        print("已创建正确结构的trade_records表")
        
        # 创建索引以提高查询性能
        cursor.execute("CREATE INDEX IF NOT EXISTS idx_trade_records_product ON trade_records(product_name);")
        cursor.execute("CREATE INDEX IF NOT EXISTS idx_trade_records_timestamp ON trade_records(timestamp);")
        
        # 提交事务
        conn.commit()
        
    except Exception as e:
        print(f"重新创建表结构时出错: {str(e)}")
    finally:
        if conn:
            conn.close()

# 测试插入功能
def test_insert():
    try:
        conn = sqlite3.connect('trade_monitor.db')
        cursor = conn.cursor()
        
        # 插入测试数据
        test_symbol = "TEST.FIXED"
        current_time = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
        
        cursor.execute(
            "INSERT INTO trade_records (product_name, direction, offset, volume, price, reason, timestamp, created_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?)",
            (test_symbol, "BUY", "OPEN", 1.0, 1000.0, "测试修复", current_time, current_time)
        )
        conn.commit()
        
        # 验证插入
        cursor.execute("SELECT COUNT(*) FROM trade_records WHERE product_name = ?", (test_symbol,))
        count = cursor.fetchone()[0]
        
        if count > 0:
            print("测试成功！记录已成功插入到修复后的表中。")
            
            # 显示表结构
            cursor.execute("PRAGMA table_info(trade_records)")
            columns = cursor.fetchall()
            print("\n修复后的表结构:")
            for col in columns:
                print(f"  {col[1]} ({col[2]})")
                
            # 显示插入的记录
            cursor.execute("SELECT * FROM trade_records WHERE product_name = ?", (test_symbol,))
            record = cursor.fetchone()
            print(f"\n插入的测试记录: {record}")
        else:
            print("测试失败！记录未成功插入。")
            
    except Exception as e:
        print(f"测试插入时出错: {str(e)}")
    finally:
        if conn:
            conn.close()

if __name__ == "__main__":
    main()